package org.firestorm.traficforecast.database;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.firestorm.traficforecast.utils.FileUtils;

public class HedgeDAO {
	private HashMap<String, Double> giaTu;
	private double omega;
	private double[][] data;

	public HedgeDAO() {
		double[][] input = getData(); 
		this.data = khoiTaoDuLieu(input);
		giaTu = khoiTaoGiaTu();
	}

	public static void main(String[] args) {
//		double[][] input = getData();
//		HedgeDAO dsgt = new HedgeDAO(input);
//		System.out.println("rat: " + dsgt.giaTu.get("rat"));
//		System.out.println("hon: " + dsgt.giaTu.get("hon"));
//		System.out.println("kha nang: " + dsgt.giaTu.get("khaNang"));
//		System.out.println("it: " + dsgt.giaTu.get("it"));
//		System.out.println("gia tri trung hoa: " + dsgt.omega);
//
//		Hedge hedge = new Hedge(dsgt.omega, dsgt.giaTu.get("rat"),
//				dsgt.giaTu.get("hon"), dsgt.giaTu.get("khaNang"),
//				dsgt.giaTu.get("it"));
//		System.out.println(updateHedge(hedge));
//
//		// double[][] data = dsgt.getData();
//		// System.out.println(data.length);
		 File inputFile = new File(
		 "E:\\HOC TAP\\KI 20141\\Project\\WorkSpace\\input.txt");
//		// dsgt.updateData(inputFile);
//		insertHedge(hedge);
		HedgeDAO hedgeD = new HedgeDAO();
		hedgeD.updateData(inputFile);
		
	}

	@SuppressWarnings("unused")
	private static void createHedgeTable() {
		MySQLConnect connect = new MySQLConnect();
		Connection conn = connect.getConnection();
		try {
			PreparedStatement stmt = conn
					.prepareStatement("CREATE TABLE hedge(middle DOUBLE, very DOUBLE, more DOUBLE, possibly DOUBLE, less DOUBLE) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unused")
	private static void insertHedge(Hedge hedge){
		MySQLConnect connect = new MySQLConnect();
		Connection conn = connect.getConnection();
		PreparedStatement stmt = null;
		try {
			stmt = conn
					.prepareStatement("INSERT INTO hedge(middle, very, more, possibly, less) VALUES(?, ?, ?, ?, ?)");
			stmt.setDouble(1, hedge.getMiddle());
			stmt.setDouble(2, hedge.getVery());
			stmt.setDouble(3, hedge.getMore());
			stmt.setDouble(4, hedge.getPossibly());
			stmt.setDouble(5, hedge.getLess());

			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			MySQLConnect.closeConnection(conn, stmt);
		}
	}
	
	public static boolean updateHedge(Hedge h) {
		MySQLConnect connect = new MySQLConnect();
		Connection conn = connect.getConnection();

		try {
			PreparedStatement stmt = conn
					.prepareStatement("UPDATE hedge SET middle = ?, very = ?, more = ?, possibly = ?, less = ?");
			stmt.setDouble(1, h.getMiddle());
			stmt.setDouble(2, h.getVery());
			stmt.setDouble(3, h.getMore());
			stmt.setDouble(4, h.getPossibly());
			stmt.setDouble(5, h.getLess());

			stmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static Hedge getHedge() {
		MySQLConnect connect = new MySQLConnect();
		Connection conn = connect.getConnection();

		PreparedStatement stmt = null;
		try {
			Hedge result = null;
			stmt = conn
					.prepareStatement("SELECT * FROM hedge");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				double mid = rs.getDouble("middle");
				double very = rs.getDouble("very");
				double more = rs.getDouble("more");
				double poss = rs.getDouble("possibly");
				double less = rs.getDouble("less");
				result = new Hedge(mid, very, more, poss, less);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			MySQLConnect.closeConnection(conn, stmt);
		}
	}
	
	public static Hedge getHedge(Connection conn){
		try {
			Hedge result = null;
			PreparedStatement stmt = conn
					.prepareStatement("SELECT * FROM hedge");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				double mid = rs.getDouble("middle");
				double very = rs.getDouble("very");
				double more = rs.getDouble("more");
				double poss = rs.getDouble("possibly");
				double less = rs.getDouble("less");
				result = new Hedge(mid, very, more, poss, less);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	private HashMap<String, Double> khoiTaoGiaTu() {
		HashMap<String, Double> result = new HashMap<String, Double>();
		if (data != null && data.length > 0) {
			double m1 = 0, m2 = 0, m3 = 0, m4 = 0;
			double o1 = 0, o2 = 0, o3 = 0, o4 = 0;
			int size = data[0].length;
			int lengh = data.length;
			for (int i = 0; i < data.length; i++) {
				m1 += (data[i][0] + data[i][size - 1]);
				o1 += data[i][0];
				m2 += (data[i][1] + data[i][size - 2]);
				o2 += data[i][1];
				m3 += (data[i][2] + data[i][size - 3]);
				o3 += data[i][2];
				m4 += (data[i][3] + data[i][size - 4]);
				o4 += data[i][3];
			}
			m1 = m1 / lengh;
			result.put("rat", m1);
			m2 = m2 / lengh;
			result.put("hon", m2);
			m3 = m3 / lengh;
			result.put("khaNang", m3);
			m4 = m4 / lengh;
			result.put("it", m4);
			this.omega = (o1 / m1 + o2 / m2 + o3 / m3 + o4 / m4) / lengh / 4;
		}
		return result;
	}

	private double[][] khoiTaoDuLieu(double[][] input) {
		double[][] result = null;
		if (input != null && input.length > 0) {
			int n = input.length;
			int m = input[0].length;
			result = new double[n][m];
			for (int i = 0; i < n; i++) {
				result[i][0] = input[i][0];
				for (int j = 1; j < m - 1; j++) {
					result[i][j] = input[i][j] - input[i][j - 1];
				}
				result[i][m - 1] = 1 - input[i][m - 2];
			}
		}
		return result;
	}

	public HashMap<String, Double> getGiaTu() {
		return giaTu;
	}

	public void setGiaTu(HashMap<String, Double> giaTu1) {
		giaTu = giaTu1;
	}

	public double getOmega() {
		return omega;
	}

	private double[][] getData() {
		ArrayList<double[]> list = new ArrayList<double[]>();
		double[][] result = null;
		MySQLConnect connect = new MySQLConnect();
		Connection conn = connect.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM hedgedata");
			while (rs.next()) {
				double[] tmp = new double[8];
				for (int i = 0; i < 8; i++) {
					tmp[i] = rs.getDouble(i + 2);
				}
				list.add(tmp);
			}
			result = new double[list.size()][8];
			list.toArray(result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			MySQLConnect.closeConnection(conn, stmt);
		}

		return result;
	}

	public void updateData(File input) {
		double[][] data = FileUtils.readFromFile(input);
		MySQLConnect connect = new MySQLConnect();
		Connection conn = connect.getConnection();
		Statement stmtTruncate = null;
		try {
			stmtTruncate = conn.createStatement();
			stmtTruncate.executeQuery("TRUNCATE TABLE hedgedata");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {

			PreparedStatement stmt = conn
					.prepareStatement("INSERT INTO hedgedata VALUES(?,?,?,?,?,?,?,?,?)");

			for (int i = 0; i < data.length; i++) {
				stmt.setDouble(1, i + 1);
				stmt.setDouble(2, data[i][0]);
				stmt.setDouble(3, data[i][1]);
				stmt.setDouble(4, data[i][2]);
				stmt.setDouble(5, data[i][3]);
				stmt.setDouble(6, data[i][4]);
				stmt.setDouble(7, data[i][5]);
				stmt.setDouble(8, data[i][6]);
				stmt.setDouble(9, data[i][7]);

				stmt.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void setOmega(double omega) {
		this.omega = omega;
	}
}
